from sqlalchemy import create_engine, MetaData
import os

# 数据库配置（根据实际情况修改）
db_config = {
    'username': 'cfsread',
    'password': 'root',
    'host': '192.168.1.65',
    'port': '3306',
    'database': 'cfs_cloud_job',  # 可以留空或指定某个库
}

# 创建数据库连接
engine = create_engine(
    f"mysql+pymysql://{db_config['username']}:{db_config['password']}@{db_config['host']}:{db_config['port']}/")
metadata = MetaData()

# 排除系统库
system_databases = ['information_schema', 'mysql', 'performance_schema', 'sys']

try:
    # 获取所有非系统库
    with engine.connect() as connection:
        result = connection.execute("SHOW DATABASES")
        all_databases = [row[0] for row in result if row[0] not in system_databases]

    # 导出每个非系统库的表结构 DDL
    with open('table_ddl.sql', 'w', encoding='utf-8') as f:
        for db in all_databases:
            f.write(f"-- Database: {db}\n\n")
            engine_db = create_engine(
                f"mysql+pymysql://{db_config['username']}:{db_config['password']}@{db_config['host']}:{db_config['port']}/{db}")
            metadata_db = MetaData()
            metadata_db.reflect(bind=engine_db)

            for table in metadata_db.tables.values():
                # 获取表的 DDL 语句
                ddl = str(metadata_db.tables[table.name].__repr__()).replace('Table(', 'CREATE TABLE ').replace(')',
                                                                                                                ';')
                f.write(f"{ddl}\n\n")

    print("表结构 DDL 已导出到 table_ddl.sql")

except Exception as e:
    print(f"发生错误: {e}")
